/**
* Copy a campaign sub tree
*
* @param integer id campaign id
* @param array   adjustments array of adjustments
* @return array
*/
function civicrm_api3_campaign_tree_clone($params)
{
    return CRM_Campaign_Tree::cloneCampaign($params['id'], 0, $params['depth'], $params);
}
예제 #2
0
 /**
  * Get Key Performance Indicators (KPIs) for a specific campaign (-subtree):
  *
  * Total Costs (sum of expenses)
  * Total revenue (sum of contributions connected with campaign)
  * Total revenue Goal (field in campaign)
  * Number/amount of Contributions (all but cancelled & failed)
  * Number/amount of Contributions (only completed)
  * Number of first contributions
  * ROI (Total revenue divided by total costs)
  * average contribution amount
  * average cost per first contribution
  * Revenue goal reached in percent (total revenue divided by total revenue goal *100)
  */
 public static function getCampaignKPI($id)
 {
     // get all sub-campaigns
     $campaigns = CRM_Campaign_Tree::getCampaignIds($id, 99);
     $ids = array();
     $ids[] = $id;
     if (count($campaigns['children']) > 0) {
         $campaigns = $campaigns['children'];
     }
     $ids_list = implode(',', $ids);
     // needed status ids
     $status = array();
     $status['completed'] = CRM_Core_OptionGroup::getValue('contribution_status', 'Completed', 'name');
     $status['cancelled'] = CRM_Core_OptionGroup::getValue('contribution_status', 'Cancelled', 'name');
     $status['failed'] = CRM_Core_OptionGroup::getValue('contribution_status', 'Failed', 'name');
     // get total revenue
     if (count($campaigns['children']) > 0) {
         $ids_list_tr = implode(',', array_merge(array($id), array_keys($campaigns)));
     } else {
         $ids_list_tr = $ids_list;
     }
     $query = "\n      SELECT    SUM(contrib.total_amount) as revenue\n      FROM  civicrm_contribution contrib\n      WHERE contrib.campaign_id IN ( {$ids_list_tr} )\n      AND   contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']})\n      ";
     $contribution = CRM_Core_DAO::executeQuery($query);
     $kpi = array();
     $total_revenue = 0.0;
     while ($contribution->fetch()) {
         $total_revenue = is_null($contribution->revenue) ? 0.0 : $contribution->revenue;
     }
     $kpi["total_revenue"] = array("id" => "total_revenue", "title" => "Total Revenue", "kpi_type" => "money", "vis_type" => "none", "description" => "Total revenue", "value" => isset($total_revenue) ? $total_revenue : 0.0, "link" => "");
     // get total revenue goal
     $query = "\n      SELECT camp.goal_revenue\n      FROM  civicrm_campaign camp\n      WHERE camp.id = {$id};\n      ";
     $campaign = CRM_Core_DAO::executeQuery($query);
     while ($campaign->fetch()) {
         $total_revenue_goal = is_null($campaign->goal_revenue) ? 0.0 : $campaign->goal_revenue;
     }
     $kpi["total_revenue_goal"] = array("id" => "total_revenue_goal", "title" => "Total Revenue Goal", "kpi_type" => "money", "vis_type" => "none", "description" => "Total revenue goal", "value" => isset($total_revenue_goal) ? $total_revenue_goal : 0.0, "link" => "");
     // get all completed and average contribution amount
     $query = "\n      SELECT   COUNT(contrib.id) as amount_completed,\n               AVG(contrib.total_amount) as amount_average\n      FROM  civicrm_contribution contrib\n      WHERE contrib.campaign_id IN ({$ids_list})\n      AND   contrib.contribution_status_id = {$status['completed']};\n      ";
     $contribution = CRM_Core_DAO::executeQuery($query);
     while ($contribution->fetch()) {
         $amount_completed = $contribution->amount_completed;
         $amount_average = is_null($contribution->amount_average) ? 0.0 : $contribution->amount_average;
     }
     $kpi["amount_completed"] = array("id" => "amount_completed", "title" => "Number of Contributions (completed)", "kpi_type" => "number", "vis_type" => "none", "description" => "Number of completed contributions", "value" => isset($amount_completed) ? $amount_completed : 0.0, "link" => "");
     $kpi["amount_average"] = array("id" => "amount_average", "title" => "Average Amount of Contributions", "kpi_type" => "money", "vis_type" => "none", "description" => "Average amount of completed contributions", "value" => isset($amount_average) ? $amount_average : 0.0, "link" => "");
     // get all but cancelled and failed
     $query = "\n      SELECT   COUNT(contrib.id) as amount_all\n      FROM  civicrm_contribution contrib\n      WHERE contrib.campaign_id IN ({$ids_list})\n      AND   contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']});\n      ";
     $contribution = CRM_Core_DAO::executeQuery($query);
     while ($contribution->fetch()) {
         $amount_all = $contribution->amount_all;
     }
     $kpi["amount_all"] = array("id" => "amount_all", "title" => "Number of Contributions (all but cancelled/failed)", "kpi_type" => "number", "vis_type" => "none", "description" => "Number of Contributions (all but cancelled/failed)", "value" => isset($amount_all) ? $amount_all : 0.0, "link" => "");
     // get all expenses
     $result = civicrm_api3('CampaignExpense', 'getsum', array('campaign_id' => $id));
     if ($result['is_error'] == 0) {
         $total_costs = $result['values'][$result['id']];
     }
     $kpi["total_cost"] = array("id" => "ttlcost", "title" => "Total Costs", "kpi_type" => "money", "vis_type" => "none", "description" => "Sum of (known) expenses to this campaign", "value" => isset($total_costs) ? $total_costs : 0.0, "link" => "");
     $query = "\n      SELECT COUNT(id)\n      FROM civicrm_contribution first_contribution\n      WHERE first_contribution.campaign_id IN ({$ids_list})\n        AND NOT EXISTS (SELECT id\n                        FROM civicrm_contribution other_contribution\n                        WHERE other_contribution.contact_id = first_contribution.contact_id\n                          AND other_contribution.receive_date < first_contribution.receive_date);";
     $first_contributions = CRM_Core_DAO::singleValueQuery($query);
     // get all first
     $kpi["amount_first"] = array("id" => "amount_first", "title" => "Number of First Contributions", "kpi_type" => "number", "vis_type" => "none", "description" => "Number of first contributions associated with this campaign", "value" => $first_contributions, "link" => "");
     // get average cost per first contribution
     $kpi['amount_average_first'] = array("id" => "amount_average_first", "title" => "Average Cost per First Contribution", "kpi_type" => "money", "vis_type" => "none", "description" => "Average Cost per first contribution associated with this campaign", "value" => $total_costs / $first_contributions, "link" => "");
     $second_or_later = $amount_all - $first_contributions;
     // get average cost per second or later contribution
     $kpi['amount_average_second_or_later'] = array("id" => "amount_average_second", "title" => "Average Cost per Second or Later Contribution", "kpi_type" => "money", "vis_type" => "none", "description" => "Average Cost per second or later contribution associated with this campaign", "value" => $second_or_later, "link" => "");
     // get ROI
     $kpi["roi"] = array("id" => "roi", "title" => "ROI", "kpi_type" => "number", "vis_type" => "none", "description" => "Return on investment", "value" => $total_revenue / ($total_costs == 0.0 ? 1.0 : $total_costs), "link" => "https://en.wikipedia.org/wiki/Return_on_investment");
     // get revenue goal reached percent
     if ($total_revenue_goal) {
         $total_revenue_goal_pc = $total_revenue / $total_revenue_goal;
     } else {
         $total_revenue_goal_pc = -1;
     }
     $kpi["total_revenue_goal_pc"] = array("id" => "total_revenue_goal_pc", "title" => "Total Revenue Reached", "kpi_type" => "percentage", "vis_type" => "none", "description" => "Total Revenue reached", "value" => $total_revenue_goal_pc, "link" => "");
     // get revenue breakdown
     $query = "\n      SELECT    SUM(contrib.total_amount) as revenue,\n                camp.title as label\n      FROM  civicrm_contribution contrib,\n            civicrm_campaign camp\n      WHERE contrib.campaign_id IN ( %s )\n      AND   contrib.campaign_id = camp.id\n      AND   contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']})\n      ";
     $e_query = sprintf($query, $id);
     $contribution = CRM_Core_DAO::executeQuery($e_query);
     while ($contribution->fetch()) {
         $revenue_current = array("label" => $contribution->label, "value" => (is_null($contribution->revenue) ? 0.0 : $contribution->revenue) / $total_revenue);
     }
     $revenue_subcampaigns = array();
     $tmp_idslist = array();
     $children = CRM_Campaign_Tree::getCampaignIds($id, 0);
     if (count($children['children']) > 0) {
         $children = $children['children'];
         foreach ($children as $c_id => $label) {
             $subcampaigns = CRM_Campaign_Tree::getCampaignIds($c_id, 99);
             $tmp_idslist[] = $c_id;
             if (count($subcampaigns['children']) > 0) {
                 $subcampaigns = $subcampaigns['children'];
                 foreach ($subcampaigns as $key => $value) {
                     $tmp_idslist[] = $key;
                 }
             }
             $id_string = implode(',', $tmp_idslist);
             $e_query = sprintf($query, $id_string);
             $curr_contrib = CRM_Core_DAO::executeQuery($e_query);
             while ($curr_contrib->fetch()) {
                 if (is_null($curr_contrib->revenue)) {
                     continue;
                 }
                 $revenue_subcampaigns[] = array("label" => $label, "value" => $curr_contrib->revenue / $total_revenue);
             }
             $tmp_idslist = array();
         }
     }
     $revenue_combined = array();
     $revenue_combined[] = $revenue_current;
     $revenue_combined = array_merge($revenue_combined, $revenue_subcampaigns);
     $kpi["revenue_breakdown"] = array("id" => "revenue_breakdown", "title" => "Revenue Breakdown", "kpi_type" => "hidden", "vis_type" => "pie_chart", "description" => "Revenue Breakdown", "value" => $revenue_combined, "link" => "");
     // get donation heartbeat
     if (count($campaigns['children']) > 0) {
         $ids_list_hb = implode(',', array_merge(array($id), array_keys($campaigns)));
     } else {
         $ids_list_hb = $ids_list;
     }
     $query_contribs = "\n      SELECT `receive_date` as date,\n              COUNT(*) as value\n      FROM  civicrm_contribution contrib\n      WHERE contrib.campaign_id IN ( {$ids_list_hb} )\n      AND contrib.contribution_status_id NOT IN ({$status['cancelled']}, {$status['failed']})\n      GROUP BY DATE(`receive_date`)\n      ;";
     $all_contribs = array();
     $contribution = CRM_Core_DAO::executeQuery($query_contribs);
     while ($contribution->fetch()) {
         $date = new DateTime($contribution->date);
         $date = $date->format('Y-m-d 00:00:00');
         $all_contribs[] = array("date" => $date, "value" => $contribution->value);
     }
     $kpi["donation_heartbeat"] = array("id" => "donation_heartbeat", "title" => "Donation Heartbeat", "kpi_type" => "hidden", "vis_type" => "line_graph", "description" => "Donation Heartbeat", "value" => $all_contribs, "link" => "");
     CRM_Utils_CampaignCustomisationHooks::campaign_kpis($id, $kpi, 99);
     return json_encode($kpi);
 }